﻿require 'open-uri'

class StockSheet < ActiveRecord::Base
  attr_accessor  :better_ord, :cheap_ord
  
  has_one :stock_info, primary_key: :symbol, foreign_key: :symbol
  
  scope :available, -> { where('fndate is not NULL') }

  def self.load_sheets(symbol)
    code = symbol[2..-1]
    
    balance_url = "http://money.finance.sina.com.cn/corp/go.php/vFD_BalanceSheet/stockid/#{code}/ctrl/part/displaytype/4.phtml"
    sheet_arr = open_sheet(balance_url, "table#BalanceSheetNewTable0")
    
    profit_url = "http://money.finance.sina.com.cn/corp/go.php/vFD_ProfitStatement/stockid/#{code}/ctrl/part/displaytype/4.phtml"
    sheet_arr.concat open_sheet(profit_url, "table#ProfitStatementNewTable0")
    
    cash_url = "http://money.finance.sina.com.cn/corp/go.php/vFD_CashFlow/stockid/#{code}/ctrl/part/displaytype/4.phtml"
    sheet_arr.concat open_sheet(cash_url, "table#ProfitStatementNewTable0")
    
    select_columns = [25,84,1,53,59,82,240,33,32,30,52,27,28,141,128,94,85,86,105,49,148,135,20]
    headers = select_columns.map { |v| sheet_arr[v][0] }.join ','
    raise "NOT A COMMON STOCK:#{symbol}"+headers unless headers == '流动资产合计,流动负债合计,货币资金,短期借款,应付票据,一年内到期的非流动负债,一年内到期的可转换公司债券,固定资产原值,投资性房地产,长期股权投资,资产总计,可供出售金融资产,持有至到期投资,三、营业利润,营业税金及附加,实收资本(或股本),长期借款,应付债券,少数股东权益,递延所得税资产,四、净利润,其中:对联营企业和合营企业的投资收益,存货'
    
    fndate = sheet_arr[0][1]
    sheet_map = {fndate: fndate}
    select_columns.each_index do |i|
      sheet_map.send :[]=, "c%.2d" % (i+1), sheet_arr[select_columns[i]][1].to_f * 10000
    end

    new_sheet = StockSheet.where(symbol: symbol).first_or_initialize
    new_sheet.update_attributes! sheet_map
    new_sheet
  end
  
  def self.calc_better_cheap
    available.sort { |a, b| a.better_v <=> b.better_v }.reverse.map.with_index(1) { |el, i| el.better_ord = i ; el}.sort { |a,b| a.cheap_v <=> b.cheap_v }.reverse.map.with_index(1) { |el, i| el.cheap_ord = i; el}.sort { |a,b| a.bc_value <=> b.bc_value }
  end
  
  def better_v
    (c14 + c15) / pure_asset
  end
  
  def cheap_v
    (c14 + c22) / pure_pay
  end
  
  def bc_value
    better_ord + cheap_ord
  end
  
  private
  
  # 超额现金，流动资产 减 流动负债，或者持有现金
  def overhead_money
    return c03 if c01 < c02
    [c03, c01 - c02].min
  end
  
  # 纯负债，流动负债 减 生产资料短期负债
  def debt
    c02 - c04 - c05 - c06 - c07
  end
  
  # 纯生产力
  def pure_asset
    c01 + c08 + c09 + c10 - overhead_money - debt
  end
  
  # 购买投入
  def pure_pay
    stock_info.mktcap + c04 + c05 + c06 + c07 + c17 + c18 + c19 + c20 - c12 - c13 - overhead_money
  end
  

  
  def self.open_sheet(url, table_tag)
    str = open(url).read
    doc = Nokogiri::HTML str
    
    data_arr = doc.css("#{table_tag} > tbody > tr").map do |tr|
      tdlist = tr.css("td")
      tdlist.map do |td|
        td.text.gsub(/,/, '').gsub(/--/, "0")
      end if tdlist.count >= 2
    end.reject { |elem| elem.nil? }
    
    i = data_arr[0].index do |elem|
      ['-12-31', '-09-31', '-06-31'].any? { |word| elem[word] }
    end
    
    data_arr.map do |elem|
      [elem[0], elem[i]]
    end
  end
  
end
